package com.ipartek.formacion.primeraWebApp.bbdd;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ipartek.primeraWebApp.beans.Alumno;
import com.ipartek.primeraWebApp.enumeraciones.Rol;
import com.ipartek.primeraWebApp.excepciones.AlumnoException;


public class ODBCAlumnoDAO implements AlumnoDAO {


	 Connection connection = null;
     PreparedStatement ptmt = null;
     ResultSet resultSet = null;
     
     private final static String TABLE_NAME     = "ALUMNOS";
     //Columnas
     private final static String  COL_ID        = "id";
     private final static String  COL_DNI	    = "dni";
     private final static String  COL_NOMBRE    = "nombre";
     private final static String  COL_APELLIDO1 = "apellido1";
     private final static String  COL_APELLIDO2 = "apellido2";
     private final static String  COL_EDAD		= "edad";
     private final static String  COL_ROL		= "rol";
     private final static String  COL_EMAIL		= "email";
     

     public ODBCAlumnoDAO() {
    	 super();
     }    
     


     
     /**
 	 * Saca el listado completo de alumnos de la Base de Datos y la mete en un
 	 * List
 	 * 
 	 * @return List<alumno> devuelve un listado de alumnos
 	 */
 	public List<Alumno> getAlumnos() {

 		List<Alumno> listaAlu = new ArrayList<Alumno>();

 		try {

 			String queryString = "SELECT * FROM " + TABLE_NAME;

 			connection = ODBCFactoryDAO.getConnection();

 			ptmt = connection.prepareStatement(queryString);

 			resultSet = ptmt.executeQuery();

 			while (resultSet.next()) {

 				Alumno alu = new Alumno();
 				alu.setDni(resultSet.getString(COL_DNI));

 				alu.setApellido1(resultSet.getString(COL_APELLIDO1));
 				alu.setApellido2(resultSet.getString(COL_APELLIDO2));
 				alu.setEdad(resultSet.getInt(COL_EDAD));
 				alu.setId(resultSet.getInt(COL_ID));
 				alu.setNombre(resultSet.getString(COL_NOMBRE));
 				alu.setEmail(resultSet.getString(COL_EMAIL));

 				int rol = resultSet.getInt(COL_ROL);

 				switch (rol) {
 				case 0:
 					alu.setRol(Rol.TRABAJADOR);
 					break;
 				case 1:
 					alu.setRol(Rol.JEFE);
 					break;
 				case 2:
 					alu.setRol(Rol.CLIENTE);
 					break;
 				}

 				listaAlu.add(alu);
 				alu = null;

 			}

 			return listaAlu;

 		} catch (AlumnoException e) {
 			e.printStackTrace();
 		} catch (SQLException e) {
 			System.out.println("\n--- SQLException caught ---\n");
 			while (e != null) {
 				System.out.println("Message: " + e.getMessage() + "\n");
 				System.out.println("SQLState: " + e.getSQLState() + "\n");
 				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 				e = e.getNextException();
 				System.out.println("");
 			}
 		} finally {

 			try {
 				if (resultSet != null)
 					resultSet.close();
 				if (ptmt != null)
 					ptmt.close();
 				if (connection != null)
 					connection.close();
 			} catch (SQLException e) {
 				System.out.println("\n--- SQLException caught ---\n");
 				while (e != null) {
 					System.out.println("Message: " + e.getMessage() + "\n");
 					System.out.println("SQLState: " + e.getSQLState() + "\n");
 					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 					e = e.getNextException();
 					System.out.println("");
 				}
 			} catch (Exception e) {
 				e.printStackTrace();
 			}

 		}
 		return listaAlu;
 	}

 	/**
 	 * busca en la Base de Datos los datos de un alumno por dni
 	 * 
 	 * @param dni
 	 *            del alumno
 	 * @return Alumno
 	 */
 	@Override
 	public Alumno getAlumnoByDni(String dni) throws AlumnoException {
 		try {
 			List<Alumno> listaAlu = new ArrayList<Alumno>();
 			String queryString = "SELECT * FROM " + TABLE_NAME + " WHERE "
 					+ COL_DNI + "='" + dni + "'";
 			connection = ODBCFactoryDAO.getConnection();
 			ptmt = connection.prepareStatement(queryString);
 			resultSet = ptmt.executeQuery();
 			Alumno alu = new Alumno();

 			while (resultSet.next()) {
 				alu.setDni(resultSet.getString(COL_DNI));
 				alu.setApellido1(resultSet.getString(COL_APELLIDO1));
 				alu.setApellido2(resultSet.getString(COL_APELLIDO2));
 				alu.setEdad(resultSet.getInt(COL_EDAD));
 				alu.setId(resultSet.getInt(COL_ID));
 				alu.setNombre(resultSet.getString(COL_NOMBRE));
 				alu.setEmail(resultSet.getString(COL_EMAIL));

 				switch (resultSet.getInt(COL_ROL)) {
 				case 0:
 					alu.setRol(Rol.TRABAJADOR);
 					break;
 				case 1:
 					alu.setRol(Rol.JEFE);
 					break;
 				case 2:
 					alu.setRol(Rol.CLIENTE);
 					break;
 				}
 				listaAlu.add(alu);
 			}
 			if (listaAlu.size() > 1) {

 				throw (new AlumnoException(
 						"ERROR: Existen mas de un usuario con el dni:" + dni));
 			} else if (listaAlu.size() == 1) {

 				return alu;
 			} else {
 				return null;
 			}

 		} catch (AlumnoException e) {
 			e.printStackTrace();
 		} catch (SQLException e) {
 			System.out.println("\n--- SQLException caught ---\n");
 			while (e != null) {
 				System.out.println("Message: " + e.getMessage() + "\n");
 				System.out.println("SQLState: " + e.getSQLState() + "\n");
 				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 				e = e.getNextException();
 				System.out.println("");
 			}
 		} finally {

 			try {
 				if (resultSet != null)
 					resultSet.close();
 				if (ptmt != null)
 					ptmt.close();
 				if (connection != null)
 					connection.close();
 			} catch (SQLException e) {
 				System.out.println("\n--- SQLException caught ---\n");
 				while (e != null) {
 					System.out.println("Message: " + e.getMessage() + "\n");
 					System.out.println("SQLState: " + e.getSQLState() + "\n");
 					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 					e = e.getNextException();
 					System.out.println("");
 				}
 			} catch (Exception e) {
 				e.printStackTrace();
 			}

 		}
 		return null;
 	}

 	/**
 	 * modifica en la Base de Datos los datos de un alumno
 	 * 
 	 * @param alu
 	 *            un alumno
 	 * @return boolean
 	 */
 	@Override
 	public boolean udapteAlumno(Alumno alu) throws AlumnoException {

 		try {
 			String dni = alu.getDni();

 			int rol = 0;
 			switch (alu.getRol().toString().charAt(0)) {
 			case 'T':
 				rol = 0;
 			case 'J':
 				rol = 1;
 			case 'C':
 				rol = 2;
 			}
 			String queryString = "UPDATE " + TABLE_NAME + " SET " + COL_NOMBRE
 					+ " ='" + alu.getNombre() + "'," + COL_APELLIDO1 + " ='"
 					+ alu.getApellido1() + "'," + COL_APELLIDO2 + " ='"
 					+ alu.getApellido2() + "'," + COL_EDAD + " ='"
 					+ alu.getEdad() + "'," + COL_EMAIL + " ='" + alu.getEmail()
 					+ "'," + COL_ROL + " ='" + rol + "'  WHERE DNI='" + dni
 					+ "'";

 			connection = ODBCFactoryDAO.getConnection();
 			ptmt = connection.prepareStatement(queryString);
 			ptmt.executeUpdate();

 			System.out.println("Datos modificados");
 		} catch (SQLException e) {
 			System.out.println("\n--- SQLException caught ---\n");
 			while (e != null) {
 				System.out.println("Message: " + e.getMessage() + "\n");
 				System.out.println("SQLState: " + e.getSQLState() + "\n");
 				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 				e = e.getNextException();
 				System.out.println("");
 			}
 		} finally {

 			try {
 				if (resultSet != null)
 					resultSet.close();
 				if (ptmt != null)
 					ptmt.close();
 				if (connection != null)
 					connection.close();
 			} catch (SQLException e) {
 				System.out.println("\n--- SQLException caught ---\n");
 				while (e != null) {
 					System.out.println("Message: " + e.getMessage() + "\n");
 					System.out.println("SQLState: " + e.getSQLState() + "\n");
 					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 					e = e.getNextException();
 					System.out.println("");
 				}
 			} catch (Exception e) {
 				e.printStackTrace();
 			}

 		}

 		return false;
 	}

 	/**
 	 * borra en la Base de Datos los datos de un alumno
 	 * 
 	 * @param dni
 	 *            dni de un alumno
 	 * @return boolean
 	 */
 	@Override
 	public boolean deleteAlumno(String dni) throws AlumnoException {
 		try {
 			String queryString = "DELETE FROM " + TABLE_NAME + " WHERE "
 					+ COL_DNI + "= '" + dni + "'";
 			connection = ODBCFactoryDAO.getConnection();
 			ptmt = connection.prepareStatement(queryString);
 			int i = ptmt.executeUpdate();
 			if (i == 1) {
 				return true;
 			} else if (i > 1) {
 				throw (new AlumnoException(
 						"ERROR: Se han eliminado mas de una fila"));
 			} else {
 				return false;
 			}

 		} catch (SQLException e) {
 			System.out.println("\n--- SQLException caught ---\n");
 			while (e != null) {
 				System.out.println("Message: " + e.getMessage() + "\n");
 				System.out.println("SQLState: " + e.getSQLState() + "\n");
 				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 				e = e.getNextException();
 				System.out.println("");
 			}
 		} finally {

 			try {
 				if (resultSet != null)
 					resultSet.close();
 				if (ptmt != null)
 					ptmt.close();
 				if (connection != null)
 					connection.close();
 			} catch (SQLException e) {
 				System.out.println("\n--- SQLException caught ---\n");
 				while (e != null) {
 					System.out.println("Message: " + e.getMessage() + "\n");
 					System.out.println("SQLState: " + e.getSQLState() + "\n");
 					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 					e = e.getNextException();
 					System.out.println("");
 				}
 			} catch (Exception e) {
 				e.printStackTrace();
 			}

 		}
 		return false;
 	}

 	/**
 	 * inserta en la Base de Datos los datos a un alumno
 	 * 
 	 * @param alu
 	 *            un alumno
 	 * @return boolean
 	 */
 	@Override
 	public boolean insertAlumno(Alumno alu) throws AlumnoException {
 		try {
 			int rol = 0;
 			switch (alu.getRol().toString().charAt(0)) {
 			case 'T':
 				rol = 0;
 				break;
 			case 'J':
 				rol = 1;
 				break;
 			case 'C':
 				rol = 2;
 				break;
 			}

 			if (getAlumnoByDni(alu.getDni()) == null) {
 				String queryString = "INSERT INTO " + TABLE_NAME + "("
 						+ COL_DNI + "," + COL_NOMBRE + "," + COL_APELLIDO1
 						+ "," + COL_APELLIDO2 + "," + COL_EDAD + "," + COL_ROL
 						+ "," + COL_EMAIL + ")VALUES ('" + alu.getDni() + "','"
 						+ alu.getNombre() + "','" + alu.getApellido1() + "','"
 						+ alu.getApellido2() + "'," + alu.getEdad() + "," + rol
 						+ ",'" + alu.getEmail() + "')";
 				connection = ODBCFactoryDAO.getConnection();
 				ptmt = connection.prepareStatement(queryString);
 				ptmt.executeUpdate();
 			} else {
 				System.out
 						.println("ERROR: ya existe el dni en la Base de Datos");
 			}

 		} catch (SQLException e) {
 			System.out.println("\n--- SQLException caught ---\n");
 			while (e != null) {
 				System.out.println("Message: " + e.getMessage() + "\n");
 				System.out.println("SQLState: " + e.getSQLState() + "\n");
 				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 				e = e.getNextException();
 				System.out.println("");
 			}
 		} finally {

 			try {
 				if (ptmt != null)
 					ptmt.close();
 				if (connection != null)
 					connection.close();
 			} catch (SQLException e) {
 				System.out.println("\n--- SQLException caught ---\n");
 				while (e != null) {
 					System.out.println("Message: " + e.getMessage() + "\n");
 					System.out.println("SQLState: " + e.getSQLState() + "\n");
 					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
 					e = e.getNextException();
 					System.out.println("");
 				}
 			} catch (Exception e) {
 				e.printStackTrace();
 			}

 		}
 		return false;

 	}     
    





}
